﻿
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_FindMail]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_FindMail];
GO
CREATE PROCEDURE [dbo].[sproc_FindMail]
    @UserName nvarchar(300),
    @KeyString nvarchar(300),
    @SearchRange int
AS
SET NOCOUNT ON

CREATE TABLE #mail (mailid int);

IF @SearchRange & 1 = 1
    INSERT INTO #mail
    SELECT mailid
        FROM TabMailList
        WHERE (MailReceiver = @UserName or (MailSender = @UserName and MailReceiver=''))
          and MailSubject like '%' + @KeyString + '%'

IF @SearchRange & 2 = 2
    INSERT INTO #mail
    SELECT mailid
        FROM TabMailList
        WHERE (MailReceiver = @UserName or  (MailSender = @UserName and MailReceiver=''))
          and MailBody like '%' + @KeyString + '%'

IF @SearchRange & 4 = 4
    INSERT INTO #mail
    SELECT mailid
        FROM TabMailList
        WHERE (MailReceiver = @UserName or  (MailSender = @UserName and MailReceiver=''))
          and ((MailReceiver like '%' + @KeyString + '%') or (MailSender like '%' +  @KeyString + '%' and MailReceiver=''))

IF @SearchRange &8 = 8
BEGIN
    INSERT INTO #mail
    SELECT mailid 
    FROM TabMailList
    WHERE (MailReceiver = @UserName or  (MailSender = @UserName and MailReceiver=''))
        and mailid in (SELECT a.mailid from TabMailAttachFiles a,uds_scope b where b.path = a.filevisualpath and b.spid = @@spid)
    
    DELETE FROM uds_scope 
        WHERE spid = @@spid
END

SELECT
    a.*,
    (select count(*) from TabMailAttachFiles where MailID=a.MailID) AS attnumber,
    b.classname as classname
FROM TabMailList a LEFT OUTER JOIN uds_class b ON a.classid = b.classid
WHERE mailid in (SELECT mailid FROM #mail)
GO